package cn.caishen.report.common.utils;

import cn.caishen.report.common.exceptions.BusinessException;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * @author LB
 */
public class ExcelUtil {

    public final static String ERROR_VALUE_LIST = "errorValueList";
    public final static String ERROR_TITLE_LIST = "errorTitleList";
    public final static String ERROR_TITLE_WIDTH_LIST = "errorTitleWidthList";
    public final static String ERROR_RESULT_LIST = "errorResult";
    public final static String ERROR_MAP = "errorMap";
    public final static String ERROR_TITLE_NAME = "titleName";
    public final static String EXCEL_TO_LIST = "excelToList";

    /**
     * 大标题的样式
     *
     * @param wb
     * @return
     */
    public static CellStyle bigTitle(XSSFWorkbook wb) {
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 14);
        //字体加粗
        font.setBold(true);
        style.setFont(font);
        //横向居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //纵向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //上细线
        style.setBorderTop(BorderStyle.THIN);
        //下细线
        style.setBorderBottom(BorderStyle.THIN);
        //左细线
        style.setBorderLeft(BorderStyle.THIN);
        //右细线
        style.setBorderRight(BorderStyle.THIN);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }

    /**
     * 小标题的样式
     *
     * @param wb
     * @return
     */
    public static CellStyle title(XSSFWorkbook wb, boolean isRequire) {
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);

        if (isRequire) {
            font.setColor(Font.COLOR_RED);
        }

        style.setFont(font);
        //横向居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //纵向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //上细线
        style.setBorderTop(BorderStyle.THIN);
        //下细线
        style.setBorderBottom(BorderStyle.THIN);
        //左细线
        style.setBorderLeft(BorderStyle.THIN);
        //右细线
        style.setBorderRight(BorderStyle.THIN);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        /*if (isRequire) {
            style.setFillForegroundColor(IndexedColors.RED.getIndex());
        }*/

        return style;
    }

    /**
     * 文字样式
     *
     * @param wb
     * @return
     */
    public static CellStyle text(XSSFWorkbook wb) {
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);

        style.setFont(font);

        //横向居左
        style.setAlignment(HorizontalAlignment.LEFT);
        //纵向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //上细线
        style.setBorderTop(BorderStyle.THIN);
        //下细线
        style.setBorderBottom(BorderStyle.THIN);
        //左细线
        style.setBorderLeft(BorderStyle.THIN);
        //右细线
        style.setBorderRight(BorderStyle.THIN);

        return style;
    }


    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {
        Object value = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                // 数字
                case NUMERIC:
                    //value = cell.getNumericCellValue() + "";
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            // 日期格式化
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        // 解析cell时候 数字类型默认是double类型的 但是想要获取整数类型 需要格式化
                        value = new DecimalFormat("0.00").format(cell.getNumericCellValue());
                    }
                    break;
                // 公式类型
                case FORMULA:
                    try {
                        value = String.valueOf(cell.getNumericCellValue());
                    } catch (IllegalStateException e) {
                        value = String.valueOf(cell.getRichStringCellValue());
                    }
                    break;
                // 字符串
                case STRING:
                    value = cell.getStringCellValue();
                    break;
                // Boolean类型
                case BOOLEAN:
                    value = cell.getBooleanCellValue() + "";
                    break;
                // 空值
                case _NONE:
                    value = "";
                    break;
                // 错误类型
                case ERROR:
                    value = "非法字符";
                    break;
                default:
                    value = "";
                    break;
            }

        }
        return value;
    }


    /**
     * @param wb         XSSFWorkbook
     * @param response   HttpServletResponse，写入response
     * @param returnName 返回的文件名
     */
    @SneakyThrows
    public static void download(Workbook wb, HttpServletResponse response, String returnName) throws IOException {

        if (!returnName.contains("xls")) {
            returnName = returnName + ".xlsx";
        }

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        wb.write(byteArrayOutputStream);

        response.setContentType("application/octet-stream;charset=utf-8");
        //保存的文件名,必须和页面编码一致,否则乱码
        returnName = response.encodeURL(new String(returnName.getBytes(), "iso8859-1"));
        response.addHeader("Content-Disposition", "attachment;filename=" + returnName);
        response.setContentLength(byteArrayOutputStream.size());

        //取得输出流
        ServletOutputStream outputStream = response.getOutputStream();
        //写到输出流
        byteArrayOutputStream.writeTo(outputStream);
        //关闭
        byteArrayOutputStream.close();
        //刷数据
        outputStream.flush();
    }


    /**
     * @param byteArrayOutputStream byteArrayOutputStream
     * @param response              HttpServletResponse，写入response
     * @param returnName            返回的文件名
     */
    @SneakyThrows
    public static void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {

        if (!returnName.contains("xls")) {
            returnName = returnName + ".xlsx";
        }

        response.setContentType("application/octet-stream;charset=utf-8");
        //保存的文件名,必须和页面编码一致,否则乱码
        returnName = response.encodeURL(new String(returnName.getBytes(), "iso8859-1"));
        response.addHeader("Content-Disposition", "attachment;filename=" + returnName);
        response.setContentLength(byteArrayOutputStream.size());

        //取得输出流
        ServletOutputStream outputStream = response.getOutputStream();
        //写到输出流
        byteArrayOutputStream.writeTo(outputStream);
        //关闭
        byteArrayOutputStream.close();
        //刷数据
        outputStream.flush();
    }

    /**
     * 动态报表导出excel
     *
     * @param list
     * @param titleList
     * @param titleWidth
     * @param titleName
     * @return
     */
    @SneakyThrows
    public static XSSFWorkbook reportToExcel(List<List<Object>> list, List<String> titleList, List<Integer> titleWidth, String titleName) {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet();

        if (Objects.isNull(titleList)) {
            throw new BusinessException("标题列表不能为空");
        }

        if (StringUtil.isEmpty(titleName)) {
            titleName = "导出EXCEL";
        }

        int widthIndex = 0;
        for (Integer width : titleWidth) {
            sheet.setColumnWidth(widthIndex, width * 50);
            widthIndex++;
        }

        CellRangeAddress cellAddresses = new CellRangeAddress(0, 0, 0, titleList.size() - 1);
        sheet.addMergedRegion(cellAddresses);

        //第一行，大标题
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        row.setHeightInPoints(36);
        cell.setCellStyle(bigTitle(wb));
        cell.setCellValue(titleName);

        //第二行，小标题
        row = sheet.createRow(1);
        row.setHeightInPoints(26);
        int titleIndex = 0;
        for (String title : titleList) {
            cell = row.createCell(titleIndex);
            cell.setCellValue(title);
            cell.setCellStyle(title(wb, false));
            titleIndex++;
        }

        CellStyle css = wb.createCellStyle();
        XSSFDataFormat format = wb.createDataFormat();

        //第三行，数据
        int index = 2;
        if (Objects.nonNull(list) && list.size() > 0) {
            //循环数据
            for (List<Object> objectList : list) {
                Row dataRow = sheet.createRow(index);

                int cellIndex = 0;
                //每一行数据
                for (Object o : objectList) {
                    Cell dataCell = dataRow.createCell(cellIndex);

                    if (Objects.isNull(o)) {
                        dataCell.setCellValue("");
                    } else {
                        if (o instanceof String) {
                            css.setDataFormat(format.getFormat("@"));
                            dataCell.setCellValue(o.toString());
                        } else if (o instanceof Integer) {
                            dataCell.setCellValue(Integer.parseInt(o.toString()));
                        } else {
                            css.setDataFormat(format.getFormat("@"));
                            dataCell.setCellValue(o.toString());
                        }
                    }

                    dataCell.setCellStyle(text(wb));
                    cellIndex++;
                }

                index++;
            }
        }

        return wb;
    }

    /**
     * EXCEL转成list
     *
     * @param wb
     * @return
     */
    @SneakyThrows
    public static List<List<Object>> excelToList(XSSFWorkbook wb) {
        if (Objects.isNull(wb)) {
            throw new BusinessException("没有找到要解析的EXCEL表，请重新上传");
        }

        List<List<Object>> list = new ArrayList<>();
        Sheet sheet = wb.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum <= 0) {
            throw new BusinessException("不能导入空的表格");
        }

        //标题行
        Row titleRow = sheet.getRow(1);
        //先设置标题行的数据
        List<Object> titleList = new ArrayList<>();
        for (int i = 0; i < titleRow.getLastCellNum(); i++) {
            Cell cell = titleRow.getCell(i);
            Object cellValue = getCellValue(cell);
            titleList.add(cellValue);
        }
        list.add(titleList);

        //设置数据
        first:
        for (int i = 2; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            List<Object> valueList = new ArrayList<>();
            if (row == null) {
                //sheet.removeRow(row);
                //int count = i - 1;
                //throw new BusinessException("文件第" + count + "行为空数据，请传入有效数据文件");
                continue;
            }

            int allNull = 0;
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if (Objects.isNull(cell)) {
                    allNull++;
                }
                Object cellValue = getCellValue(cell);
                valueList.add(cellValue);
            }

            //全部单元格为null时，不加入 list
            if (allNull != (row.getLastCellNum() - 1)) {
                list.add(valueList);
            }
        }

        return list;
    }
}
